import unittest
import MySQLdb


def get_conn():
    conn = MySQLdb.connect(
        host='localhost',
        port=3306,
        user='root',
        passwd='qq991264921',
        db='shiyan',
        charset='utf8'
    )
    return conn


class TestMysqlClient(unittest.TestCase):

    def test_select(self):
        conn = get_conn()
        cur = conn.cursor()
        cur.execute("select * from book where id = %s" % 2)
        result = []
        for row in cur.fetchall():
            result.append(row)
        print(result)
        cur.close()
        conn.close()

    def test_update(self):
        conn = get_conn()
        cur = conn.cursor()
        cur.execute("update book set title = '%s' where id = %s" % ('test', 1))
        conn.commit()
        cur.close()
        conn.close()

    def test_delete(self):
        conn = get_conn()
        cur = conn.cursor()
        cur.execute("delete from book where id = %s" % 5)
        conn.commit()
        cur.close()
        conn.close()

    def test_insert(self):
        conn = get_conn()
        cur = conn.cursor()
        cur.execute("""
        insert into book(id, title, isbn, author_id) values(null, '%s', '%s', %s)
        """ % ('test1', 'test11张', 1))
        conn.commit()
        cur.close()
        conn.close()


if __name__ == '__main__':
    unittest.main()
